約 114,616 件
https://w.atwiki.jp/sqlpuzzle/pages/20.html
問題 kunさんの答え 2008年04月01日18時28分59秒.pdf サブクエリでやりたいこと→一人暮らしでない人のidの集合を出したい このクエリでやりたいこと→一人暮らしでない人で、且fam IS NULLなら世帯主。 やりたいことはわかるのだけれども、サブクエリのSelect節のcon_idはグループ化されたもので統一である値か集約値しかもってこれないはずなので、これではエラーになるのでは。 mysqlで試してみた(chiemin) とりあえずサブクエリだけ。 mysql select con_id from Consumers group by address having count(*) =2; +--------+ | con_id | +--------+ | 1 | | 3 | +--------+ 2 rows in set (0.00 sec) 偶然だけど、世帯主だけ出ちゃいました。これは世帯主が偶然一番最初に挿入されていたから。mysqlの場合はSelect節に集約値以外の属性が来た場合はグループの一番最初のタプルの値を出すみたいです。多分。 ゼミ中に出たコメント この問題って何がやりたいんだろう…世帯主だけにメールを送るだけならわかるんだけど、世帯主を削除しちゃうって…なぜ？ せっかく人のIDと世帯主の参照IDがあるのにaddressが一致するかどうかで同じ世帯に住んでいることを決めてしまってよいのか？ 例えば2世帯住宅とかという場合はないのだろうか？ 1人住まいでない世帯主を求める問合せ SELECT c1.con_id FROM Consumers c1, Consumers c2 WHERE c1.con_id = c2.fam でもこれだとこの問題ならいいけど（親子関係が一階層だから）、任意の階層を持ってたりしたらまずいよね（木構造みたいに） その辺はどうやらこのテーブルの設計自体がSQL向きでないもよう（サポートページより） コメント 名前 コメント すべてのコメントを見る
https://w.atwiki.jp/sqlpuzzle/pages/40.html
問題 テーブルの１列に格納された一意な数が、歯抜けのない完全な連番を成しているか、それともギャップ（１つ以上連続する欠番）を含むかどうかを調べる。 CREATE TABLE Numbers (seq INTEGER NOT NULL PRIMARY KEY); (Numbers 自然数テーブル、seq 連番) chiakiの解答 考え方 欠番があるかどうかだけ調べたいなら、数の個数が（最大の数ー最小の数＋１）個あれば良い。 注１）seq は PRIMARY KEY なので、同じ数字はないという前提の下である。 注２）連番と書いてあるので、数が順に並んでいない場合は考えていない。 SQL文 SELECT CASE WHEN count(N2.seq) = (SELECT (max(N1.seq) - min(N1.seq) +1) FROM Numbers N1) THEN has no missing number ELSE exist missing number END AS Numbers FROM Numbers N2; 実行結果 seq ----- 2 3 5 7 8 14 20 の場合 numbers ---------------------- exist missing number seq ----- 2 3 4 5 6 7 8 の場合 numbers ----------------------- has no missing number 値が１個や２個の場合も成功しました☆
https://w.atwiki.jp/eco_yotuba/pages/39.html
※注意 以下の情報は自己満足や身内による評価である可能性があります。 信じるも信じないも個人の自由でお願いします。 強い弱いよりも人物の特徴を書いていきましょう。 誹謗中傷はやめましょう。 以上を了承の上、お楽しみください。 参加者情報は自軍がもっとも詳しいはず。どんどん編集していきましょう！ 参加率は A＞B＞Cの3段階評価でお願いします。 南軍 参加者 南軍 参加者 ファイター系ソードマン系列 フェンサー系列 アーチャー系列 スカウト系列 スペルユーザー系シャーマン系列 ウァテス系列 ウォーロック系列 バックパッカー系ファーマー系列 レンジャー系列 マーチャント系列 ノービス DEM コメント欄（Wikiの編集の苦手な方はこちらに） ファイター系 ソードマン系列 フェンサー系列 アーチャー系列 スカウト系列 スペルユーザー系 シャーマン系列 ウァテス系列 ウォーロック系列 バックパッカー系 ファーマー系列 レンジャー系列 マーチャント系列 ノービス DEM コメント欄（Wikiの編集の苦手な方はこちらに） 名前
https://w.atwiki.jp/sqlpuzzle/pages/16.html
7つもあったのに私の解答例みたいなのはありませんでした。ちょっと苦しまぎれすぎた？ -- (chiemin) 2008-04-01 18 14 47
https://w.atwiki.jp/u_examination/pages/142.html
トップページ＞古文＞古文の勉強の仕方 基礎からのジャンプアップノート古典文法・演習ドリル[amazon] コメント パクリ疑惑はあるがｗ内容はいい。おすすめ -- 名無しさん (2010-11-20 16 01 36) パクリとか言ってる奴は内容をロクに見比べずに発言してばバカだから気にしないようにｗ 河合のとは内容違うし。 -- 名無しさん (2010-12-28 03 42 23) パクリとか言ってる奴は内容をロクに見比べずに発言してるバカだから気にしないようにｗ 河合のとは内容違うし。 -- 名無しさん (2010-12-28 03 42 43) ごちゃごちゃとうるせえんだよ -- 名無しさん (2011-03-24 11 57 51) 名前 コメント
https://w.atwiki.jp/sqlpuzzle/pages/12.html
Puzzle23：雑誌と売店 問題 雑誌販売を管理するためのテーブルTitles, Newsstands, Salesがある。 以下の条件をどちらかでも満たしているすべての売店を求めよ。 1. SKUが2667番と48632番の二つの雑誌について、net_sold_qty(売上部数）の平均がともに2より大きい（一方でも2以下であればその売店は選択しない） 2. SKUが1107番の雑誌についてnet_sold_qtyの平均が5より大きい（この条件を満たすなら1の条件を満たすか否かにかかわらず選択する） chieminの解答 ごめんなさい、どうすれば良いか分からなかったので、かなり苦しまぎれです。 手続き的言語モードになっているのだろうか…。 select n.stand_name from ( select N.stand_nbr as stand_nbr,magazine_sku,avg(net_sold_qty) as avg, case when magazine_sku=1107 then 100 else 1 end pnt from Titles T, Sales S where T.product_id = S.product_id and magazine_sku in (2667,48632,1107) group by S.stand_nbr,magazine_sku having avg(net_sold_qty) =2) s, Newsstands n where s.stand_nbr=n.stand_nbr group by s.stand_nbr having sum(avg*pnt) =500 or (sum(avg*pnt) between 4 and 200); +-------------+ | stand_name | +-------------+ | Newsstands2 | | Newsstands3 | +-------------+ 2 rows in set (0.00 sec) 内側の副問合せでやってること Title, Salesを結合しskuが2667,48632,1107のものだけ選択 stand_nbr,magazine_skuでグループ化し売上平均が2以上のものだけを選択 Select節に pnt属性として1107の場合のみ100、それ以外は1の値を出力 外側の問い合わせでやっていること 売上平均にpntをかけ合わせて合計 skuが1107の場合は必ず200以上になる 200以下の場合は4以上になればよい。 合計値が1107の雑誌以外で合計値が200を超えてしまうと成立しなくなるが、その場合はskuが1107の場合につけるpntの値を調節すれば良い。 fumikoさんの解答 2008年04月01日13時04分15秒.pdf 7つもあったのに私の解答例みたいなのはありませんでした。ちょっと苦しまぎれすぎた？ -- (chiemin) 2008-04-01 18 14 47 fumikoさんの解答はばっちり解答例5と一緒でした。でもなぜか答えが出てこない… -- (chiemin) 2008-04-01 18 15 27 puzzle23 コメント すべてのコメントを見る
https://w.atwiki.jp/sqlpuzzle/pages/19.html
集約関数の中にdistinctを入れられるのを初めて知りました（汗 -- (chiemin) 2008-04-01 18 24 31
https://w.atwiki.jp/sqlpuzzle/pages/17.html
fumikoさんの解答はばっちり解答例5と一緒でした。でもなぜか答えが出てこない… -- (chiemin) 2008-04-01 18 15 27
https://w.atwiki.jp/sqlpuzzle/pages/23.html
問題 Customers 顧客テーブル（customer_id 顧客ID, acct_balance 売掛金残高） Orders 注文テーブル（customer_id 顧客ID, order_id 注文ID） OrderDetails 注文明細テーブル（order_id 注文ID, item_id 製品ID, item_qty 注文数量） Products 製品テーブル（item_id 製品ID, item_qty_on_hand 現存数量） 全ての製品を購入した顧客全員の買掛金残高の平均と、全てではないがいくつかの製品を購入した顧客全員の買掛金残高の平均を求めたい。 *chiakiさんの答え ※chiakiさんはゼミ準備で大学に来れなかったので、以下のSQLは検証してません。次の見出しで検証を行います。 二つの平均を同時に出力する方法を思いつかなかったので、それぞれの場合についてSQL文を考えた。 全ての製品購入 SELECT avg(C.acct_balance) FROM Customers C, Order O, OrderDetails D, Product P WHERE count(DISTINCT D.item_id) = ( SELECT count(*) FROM Products ) AND D.item_id = P.item_id AND O.order_id = D.order_id AND C.customer_id = O.customer_id GROUP BY O.customer_id 考え方 Customer_idでグループ化し、顧客それぞれについて製品総数と等しいかどうか調べる いくつかの製品購入 SELECT avg(C.acct_balance) FROM Customers C, Order O, OrderDetails D, Product P WHERE count(DISTINCT D.item_id) ( SELECT count(*) FROM Products ) AND count(DISTINCT D.item_id) 0 AND D.item_id = P.item_id AND O.order_id = D.order_id AND C.customer_id = O.customer_id GROUP BY O.customer_id 考え方 Customer_idでグループ化し、顧客それぞれについて製品総数より少ないかどうか調べる kunさんの答え 2008年04月08日12時10分34秒.pdf すべての製品購入 select avg(acct_balance) from ( select acct_balance from ( select * from customers c , orders o , orderdetails od where o.order_id = od.order_id and o.customer_id = c.customer_id)i1)i2 group by customer_id having count(distinct item_id) = (select count(*) from products); 考え方 customers , orders , orderdetailsを自然結合する。 customer_idでグループ化し、それぞれのグループのitem_idの数が製品の種類の数と等しければ、すべての製品を購入した顧客が求まる。(製品のすべての種類の数は一番内側のサブクエリで求まる) 以上をサブクエリとして、ずべての製品を購入した顧客のcustomer_idとacct_balanceのテーブルを返す。 一番外側のselect句はそれらのacct_balanceの平均値を出力する。 結果 エラーが出ました！ ERROR column i2.item_id does not exist 行 8 having count(distinct i2.item_id) = (select count(*) ^ いくつかの製品購入 select avg(acct_balance) from ( select acct_balance from ( select * from customers c , orders o , orderdetails od where o.order_id = od.order_id and o.customer_id = c.customer_id)i1)i2 group by customer_id having count(distinct item_id) (select count(*) from products); 考え方 すべての製品購入のクエリと同じ考え方だが、havingの条件はitem_idの数が製品のすべての種類の数と等しくないものに書き変える。 結果 エラーが出ました！ ERROR column "item_id" does not exist 行 8 having count(distinct item_id) (select count(*) ^ ☆おさらい☆ ★FUMIKOさんが解説してくれた解2とYUKAさんが考えた解のコストを比較します。「explain」を使ってSQL文の処理を見ます。 postgres使用 解2をEXPLAINした結果 Aggregate (cost=108142.67..108142.68 rows=1 width=14) (actual time=0.649..0.64 9 rows=1 loops=1) InitPlan - Aggregate (cost=34.25..34.26 rows=1 width=4) (actual time=0.063..0.063 rows=1 loops=1) - Seq Scan on products (cost=0.00..29.40 rows=1940 width=4) (actua l time=0.004..0.006 rows=3 loops=1) - Seq Scan on customers c1 (cost=0.00..108104.50 rows=1562 width=14) (actu al time=0.379..0.606 rows=4 loops=1) Filter ($0 (subplan)) SubPlan - Aggregate (cost=68.83..68.84 rows=1 width=4) (actual time=0.082. .0.082 rows=1 loops=6) - Hash Join (cost=34.38..68.80 rows=9 width=4) (actual time= 0.038..0.043 rows=2 loops=6) Hash Cond (orderdetails.order_id = orders.order_id) - Seq Scan on orderdetails (cost=0.00..27.70 rows=1770 width=8) (actual time=0.002..0.007 rows=11 loops=4) - Hash (cost=34.25..34.25 rows=10 width=4) (actual tim e=0.015..0.015 rows=1 loops=6) - Seq Scan on orders (cost=0.00..34.25 rows=10 w idth=4) (actual time=0.005..0.006 rows=1 loops=6) Filter (customer_id = $1) Total runtime 20.154 ms YUKAさんの解をEXPLAINした結果 fumiko=# explain fumiko-# select avg(C1.acct1),avg(C2.acct2) fumiko-# from( fumiko(# select C.acct_balance as acct1 fumiko(# from Customers as C,Orders as O,OrderDetails as OD,Products as P fumiko(# where C.customer_id = O.customer_id fumiko(# and O.order_id = OD.order_id fumiko(# group by C.customer_id fumiko(# having count(DISTINCT OD.item_id) = count(DISTINCT P.item_id)) as C1, fumiko-# ( fumiko(# select AVG(C.acct_balance) as acct2 fumiko(# from Customers as C,Orders as O,OrderDetails as OD,Products as P fumiko(# where C.customer_id = O.customer_id fumiko(# and O.order_id = OD.order_id fumiko(# group by C.customer_id fumiko(# having count(DISTINCT OD.item_id) count(DISTINCT P.item_id)) as C2; ERROR column "c.acct_balance" must appear in the GROUP BY clause or be used in an aggregate function from句内の一つ目の問い合わせでなぞのエラーが出ました。
https://w.atwiki.jp/sqlpuzzle/pages/9.html
関連ブログ @wikiのwikiモードでは #bf(興味のある単語) と入力することで、あるキーワードに関連するブログ一覧を表示することができます 詳しくはこちらをご覧ください。 ＝＞http //atwiki.jp/guide/17_161_ja.html たとえば、#bf(ゲーム)と入力すると以下のように表示されます。 #bf